package com.ssyt.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.List;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.Date;





public class JdbcUtil {

	public static Connection getConnection() {
		Connection conn = null;
		try {
			Class.forName(JdbcConstants.DRIVER);
			conn = DriverManager.getConnection(JdbcConstants.URL, JdbcConstants.USERNAME,
					JdbcConstants.PASSWORD);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}
	/*
	 * 关闭数据库连接资源
	 */
	public static void close(Object... objects) {
		try {
			if (ArraysUtil.isNotEmpty(objects)) {
				for (Object object : objects) {
					if (object instanceof ResultSet) {
						ResultSet res = (ResultSet) object;
						if (res != null && !res.isClosed())
							res.close();
					}
					if (object instanceof PreparedStatement) {
						PreparedStatement pst = (PreparedStatement) object;
						if (pst != null && !pst.isClosed())
							pst.close();

					}
					if (object instanceof Statement) {
						Statement st = (Statement) object;
						if (st != null && !st.isClosed())
							st.close();
					}
					if (object instanceof Connection) {
						Connection conn = (Connection) object;
						if (conn != null && !conn.isClosed())
							conn.close();

					}
				}
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 数据更新操作<br>
	 * 注：此方法仅用于单个更新操作，如果有事务的情况还得手动控制Connection并开启事务操作！
	 * 
	 * @param sql
	 *            传入预编译的sql语句
	 * @param paramList
	 *            向模版中?赋值的参数
	 * @return 受影响的行数
	 * @throws RuntimeException
	 *             如果更新失败会返回此异常
	 */
	public static int executeUpdate(String sql, List<Object> paramList) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = getConnection();
			preparedStatement = connection.prepareStatement(sql);
			setParameter(paramList, preparedStatement);
			
			return preparedStatement.executeUpdate();
		} catch (SQLException e) {
			throw new RuntimeException("数据更新失败");
		} finally {
			close(connection, preparedStatement);
		}
	}

	public static int executeUpdate(String sql, Object... params) {
		return executeUpdate(sql, Arrays.asList(params));
	}
	/**
	 * 参数设置
	 * 
	 * @param paramList
	 * @param preparedStatement
	 * @throws SQLException
	 */
	public static void setParameter(List<Object> paramList,
			PreparedStatement preparedStatement) throws SQLException {
		if (paramList != null) {
			for (int i = 0; i < paramList.size(); i++) {
				preparedStatement.setObject(i + 1, paramList.get(i));
			}
		}
	}
	/**
	 * 查询：返回对象集合
	 * 
	 * @param clazz
	 * @param sql
	 * @param paramList
	 * @return
	 */
	public static <T> List<T> executeQuery(Class<T> clazz, String sql,
			List<Object> paramList) {
		List<T> resultList = new ArrayList<T>();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = getConnection();
			preparedStatement = connection.prepareStatement(sql);
			setParameter(paramList, preparedStatement);
			resultSet = preparedStatement.executeQuery();
			// 读取数据库表结构
			ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
			// 得到数据库多少列总数
			int columns = resultSetMetaData.getColumnCount();
			// 循环每条结果，并把结果封装成具体的实体对象，最后放到返回集合中
			while (resultSet.next()) {
				T obj = clazz.newInstance();
				// 对每列循环，把对应的值赋对obj的相应属性
				for (int i = 1; i <= columns; i++) {
					// getColumnLabel会去查询数据的别名，如果没有别名就取原名字
					String columnLabel = resultSetMetaData.getColumnLabel(i);
					// 循环所有的属性
					Field[] fields = clazz.getDeclaredFields();
					for (Field field : fields) {
						field.setAccessible(true);
						Class<?> fieldClass = field.getType();
						String fieldName = field.getName();
						// 某个属性有大写，表示需要属性名与数据库列名对应
						if (isContainsAlphaUpper(fieldName)) {
							String columnName = toColumnName(fieldName);
							if (columnName.equals(columnLabel)) {
								setFieldValue(resultSet, obj, columnLabel,
										field, fieldClass);
								break;
							}
						} else {
							// 表示属性名与数据库列名相同
							if (fieldName.equals(columnLabel)) {
								setFieldValue(resultSet, obj, columnLabel,
										field, fieldClass);
								break;
							}
						}
					}
				}
				resultList.add(obj);
			}
			return resultList;
		} catch (SQLException | InstantiationException | IllegalAccessException
				| IllegalArgumentException | SecurityException e) {
			throw new RuntimeException("数据查询失败");
		} finally {
			close(connection, preparedStatement, resultSet);
		}
	}
	public static <T> List<T> executeQuery(Class<T> clazz, String sql,
			Object... params) {
		return executeQuery(clazz, sql, Arrays.asList(params));
	}
	/**
	 * 查询：返回单一对象
	 * 
	 * @param clazz
	 * @param sql
	 * @param paramList
	 * @return
	 */
	public static <T> T getUniqueResult(Class<T> clazz, String sql,
			List<Object> paramList) {
		List<T> list = executeQuery(clazz, sql, paramList);
		if (list.isEmpty()) {
			return null;
		} else {
			return list.get(0);
		}
	}
	public static <T> T getUniqueResult(Class<T> clazz, String sql,
			Object... params) {
		return getUniqueResult(clazz, sql, Arrays.asList(params));
	}
	
	
	/**
	 * 查询：返回分页对象
	 * 
	 * @param clazz
	 * @param sql
	 * @param paramList
	 * @return
	 */
	public static <T> JdbcPage<T> executeQueryByPage(Class<T> clazz, int pageSize, int pageNo,
			String sql, List<Object> paramList) {
		// 数据集合
		List<T> list = executeQuery(clazz, sql, paramList);

		// 分页总数
		sql = sql.toUpperCase();
		String countSql = "SELECT COUNT(*) "
				+ sql.substring(sql.indexOf("FROM"), sql.indexOf("LIMIT"));
		int count = getCount(countSql, paramList);

		return new JdbcPage<T>(list, count, pageSize, pageNo);
	}

	/**
	 * 获取分页总数
	 * 
	 * @param countSql
	 * @param paramList
	 * @return
	 */
	private static int getCount(String countSql, List<Object> paramList) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = getConnection();
			preparedStatement = connection.prepareStatement(countSql);
			setParameter(paramList.subList(0, paramList.size() - 2), preparedStatement);
			resultSet = preparedStatement.executeQuery();
			resultSet.next();
			return resultSet.getInt(1);
		} catch (Exception e) {
			throw new RuntimeException("分页查询总数失败");
		} finally {
			close(connection, preparedStatement, resultSet);
		}
	}

	/**
	 * 查询：返回分页对象
	 * 
	 * @param clazz
	 * @param sql
	 * @param paramList
	 * @return
	 */
	public static <T> JdbcPage<T> executeQueryByPage(Class<T> clazz, int pageSize, int pageNo,
			String sql, Object... params) {
		return executeQueryByPage(clazz, pageSize, pageNo, sql, Arrays.asList(params));
	}
	
	
	
	
	
	
	/**
	 * 判断字符串里是否有大写字母
	 * @param fieldName
	 * @return
	 */
	private static boolean isContainsAlphaUpper(String fieldName) {
		for (int i = 0; i < fieldName.length(); i++) {
			int ascii = (int) fieldName.charAt(i);
			if (ascii >= 65 && ascii <= 90) {
				return true;
			}
		}
		return false;
	}	
	/**
	 * Java属性 -> 数据库字段名：helloWorld -> hello_world
	 * @param fieldName
	 * @return
	 */
	private static String toColumnName(String fieldName) {
		char[] chars = fieldName.toCharArray();
		StringBuilder sb = new StringBuilder();
		for (char c : chars) {
			int ascii = (int) c;
			if (ascii >= 65 && ascii <= 90) {
				sb.append("_" + (char) (c + 32));
			} else {
				sb.append(c);
			}
		}
		return sb.toString();
	}
	/**
	 * 利用反射实现JavaBean的自动赋值
	 * @param resultSet
	 * @param obj
	 * @param columnName
	 * @param field
	 * @param fieldClass
	 * @throws IllegalAccessException
	 * @throws SQLException
	 */
	private static <T> void setFieldValue(ResultSet resultSet, T obj,
			String columnName, Field field, Class<?> fieldClass)
			throws IllegalAccessException, SQLException {
		// FIXME 根据业务扩展
		if (fieldClass.equals(int.class) || fieldClass.equals(Integer.class)) {
			field.set(obj, resultSet.getInt(columnName));
		} else if (fieldClass.equals(String.class)) {
			field.set(obj, resultSet.getString(columnName));
		} else if (fieldClass.equals(BigDecimal.class)) {
			field.set(obj, resultSet.getBigDecimal(columnName));
		} else if (fieldClass.equals(Date.class)) {
			field.set(obj, resultSet.getTimestamp(columnName));
		}
	}

}
